﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.Common;
using System.Data.EntityClient;
using System.Data.SqlServerCe;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Xml.Linq;
using AzureDiagMon.Common;
using AzureDiagMon.Common.Interfaces;
using AzureDiagMon.DatabaseLayer.Common;
using AzureDiagMon.Model;
using AzureDiagMon.Common.Helpers;

namespace AzureDiagMon.DB.SqlCE4
{
    public class SqlCe4DatabaseLayer : DatabaseLayer.Common.DatabaseLayer
    {

        #region Overrides of DatabaseLayer

        protected override string GetDatabaseFileName()
        {
            return String.Concat(PathHelper.AppName, ".sdf");
        }

        #endregion

        protected override void InitInternal()
        {
            Debug.WriteLine("Initializing SQLCE 4 database");
            if (EntityConnectionStringBuilder.Provider != "System.Data.SqlServerCe.4.0")
            {
                throw new InvalidDataProviderException(String.Format("Invalid data provider: {0}",
                                                                     EntityConnectionStringBuilder.Provider));
            }

            ReplaceProviderInSSDL("System.Data.SqlServerCe.4.0", "4.0");
            ReplaceSQLCEDatabaseFilename();

            if (!File.Exists(GetDatabasePath()))
            {
                Debug.WriteLine("Database not found. Create new...");
                CreateSqlCEDatabase();
                Debug.WriteLine("Database created.");
            }

            CheckSqlCEConnection();
        }

        private void ReplaceSQLCEDatabaseFilename()
        {
            if (ProviderConnectionStringBuilder["Data Source"] != null)
            {
                ProviderConnectionStringBuilder.Remove("Data Source");
            }
            var fullFileName = GetDatabasePath();
            var path = Path.GetDirectoryName(fullFileName);
            if (!Directory.Exists(path))
                Directory.CreateDirectory(path);
            ProviderConnectionStringBuilder.Add("Data Source", fullFileName);
            EntityConnectionStringBuilder.ProviderConnectionString = ProviderConnectionStringBuilder.ConnectionString;
        }

        private static void CheckSqlCEConnection()
        {
            Debug.WriteLine("Checking database connection...");
            using (var conn = new SqlCeConnection(ProviderConnectionStringBuilder.ConnectionString))
            {
                conn.Open();
                Debug.WriteLine("Database connected.");
                conn.Close();
            }
        }

        private void CreateSqlCEDatabase()
        {
            var engine = new SqlCeEngine(ProviderConnectionStringBuilder.ConnectionString);
            engine.CreateDatabase();

            using (var conn = new SqlCeConnection(ProviderConnectionStringBuilder.ConnectionString))
            {
                conn.Open();
                var statements = GetDatabaseCreationScriptStatements();
                foreach (var statement in statements)
                {
                    try
                    {
                        var strippedStatement = StripSqlStatement(statement);
                        try
                        {
                            var cmd = new SqlCeCommand(strippedStatement, conn);
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            //Skip not exist exceptions on drop statements
                            if (strippedStatement.TrimStart().StartsWith("DROP"))
                            {
                                if (ex.Message.StartsWith("The specified table does not exist."))
                                {
                                    continue;
                                }
                            }
                            throw;
                        }
                    }
                    catch (Exception ex)
                    {
                        conn.Close();
                        File.Delete(GetDatabasePath());
                        throw new Exception(String.Format("Error while executing the following statement: {0}", statement), ex);
                    }
                }
            }

        }
    }
}
